package com.solcoo.controller;

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.solcoo.json.DownloadToExcelByYongHuJson;
import com.solcoo.json.ShanghuSimpleJson;
import com.solcoo.service.DataService;

@Controller
public class DataController {
	
	private final static String REPORT_PATH = "static/pic/report/";
	
	@Resource
	private DataService dataService;
	
	@RequestMapping(value = "data.jspDo")
	public ModelAndView getData(HttpServletRequest request) throws Exception {
		String picPath = request.getSession().getServletContext().getRealPath(REPORT_PATH) + "/data/";
		
		Calendar cal = Calendar.getInstance();
		
		String year = cal.get(Calendar.YEAR) + "";
		
		dataService.createYongHuCountByDay30(picPath); // 近 30 天每日新增用户数量折线图
		dataService.createSsksByDay30(picPath, null); // 近 30 天日实收款折线图
		
		ModelAndView view = new ModelAndView();
		
		view.addObject("curYear", year); // 当前年
		view.addObject("totalSsk", dataService.getTotalSskByYear(year, null)); // 年实收款总数
		view.addObject("totalYongHuCount", dataService.getTotalYongHuCountByYear(year)); // 年用户总数
		view.addObject("totalShangHuCount", dataService.getTotalShangHuCountByYear(year)); // 年商户总数
		view.addObject("ssks", dataService.getSsks(year, picPath, null)); // 月实收款数组
		view.addObject("yongHuCounts", dataService.getYongHuCount(year, picPath)); // 月用户数量数组
		view.addObject("businessCounts", dataService.getBusinessCountByYearAndMonth(year, picPath, null)); // 月交易笔数数组
		view.addObject("messageCounts", dataService.getMessageCountByYearAndMonth(year, picPath, null)); // 月发短信数量
		view.addObject("merchantCounts", dataService.getMerchantCountByYearAndMonth(year, picPath)); // 商户数量数组
		
		view.setViewName("admin/data");
		
		return view;
	}
	
	@RequestMapping(value = "shanghuData.jspDo")
	public ModelAndView getShanghuData() throws Exception {
		ModelAndView view = new ModelAndView();
	
		view.addObject("shanghuDatas", dataService.getShanghuData());
		
		view.setViewName("admin/shanghuData");
		
		return view;
	}
	
	@RequestMapping(value = "shanghumxData.jspDo")
	public ModelAndView getShanghumxData(Long yonghum, HttpServletRequest request) throws Exception {
		String picPath = request.getSession().getServletContext().getRealPath(REPORT_PATH) + "/data/";
		
		Calendar cal = Calendar.getInstance();
		
		String year = cal.get(Calendar.YEAR) + "";
		
		ModelAndView view = new ModelAndView();
		
		view.addObject("curYear", year); // 当前年
		
		if (yonghum != null) {
			view.addObject("shanghumc", "( " + dataService.getShanghumcByYonghum(yonghum) + " )"); // 商户名称
		}
		
		view.addObject("ssksByShanghu", dataService.getSsks(year, picPath, yonghum)); // 商户月实收款数组
		view.addObject("vipCountsByShanghu", dataService.getVipCount(year, picPath, yonghum)); // 商户月会员数量数组
		view.addObject("businessCountsByShanghu", dataService.getBusinessCountByYearAndMonth(year, picPath, yonghum)); // 商户月交易笔数数组
		view.addObject("messageCountsByShanghu", dataService.getMessageCountByYearAndMonth(year, picPath, yonghum)); // 商户月发短信数量数组
		view.addObject("addVipsByShanghu", dataService.getAddVipsByShanghu(year, picPath, yonghum)); // 商户月新增会员数量数组
		
		view.setViewName("admin/shanghumxData");
		
		return view;
	}
	
	@RequestMapping(value = "queryData.jspDo")
	public ModelAndView getQueryData(Long yonghum, HttpServletRequest request) throws Exception {
		String picPath = request.getSession().getServletContext().getRealPath(REPORT_PATH) + "/data/";
		
		Calendar cal = Calendar.getInstance();
		
		String year = cal.get(Calendar.YEAR) + "";
		
		ModelAndView view = new ModelAndView();
		
		view.addObject("curYear", year); // 当前年
		
		if (yonghum != null) {
			view.addObject("shanghumc", "( " + dataService.getShanghumcByYonghum(yonghum) + " )"); // 商户名称
		}
		
		view.addObject("ssksByShanghu", dataService.getSsks(year, picPath, yonghum)); // 商户月实收款数组
		view.addObject("vipCountsByShanghu", dataService.getVipCount(year, picPath, yonghum)); // 商户月会员数量数组
		view.addObject("businessCountsByShanghu", dataService.getBusinessCountByYearAndMonth(year, picPath, yonghum)); // 商户月交易笔数数组
		view.addObject("messageCountsByShanghu", dataService.getMessageCountByYearAndMonth(year, picPath, yonghum)); // 商户月发短信数量数组
		view.addObject("addVipsByShanghu", dataService.getAddVipsByShanghu(year, picPath, yonghum)); // 商户月新增会员数量数组
		view.addObject("shanghuid", yonghum);
		
		view.setViewName("data");
		
		return view;
	}
	
	@RequestMapping(value = "shanghumc.jspDo", method = RequestMethod.POST)
	public @ResponseBody List<ShanghuSimpleJson> getShanghumc() throws Exception {
		return dataService.getShanghuSimple();
	}
	
	@RequestMapping(value = "downloadToExcelByYongHu.jspDo", method = RequestMethod.GET)
	public void downloadToExcelByYongHu(HttpServletRequest request, HttpServletResponse response) throws Exception {
		response.setCharacterEncoding("utf-8");
		response.setContentType("multipart/form-data");

		response.setHeader("Content-Disposition", "attachment;fileName=download.xls");
		
		HSSFWorkbook workbook = new HSSFWorkbook(); 
		
		HSSFSheet sheet = workbook.createSheet("用户使用情况");
		
		sheet.setDefaultColumnWidth(18); // 默认列宽
		
		String[] titles = {"注册时间", "搜卡号", "手机号码", "实收总额(元)", "充值总额(元)", "支付总额(元)"};
		
		HSSFRow titleRow = sheet.createRow(0); // 创建标题行
		
		titleRow.setHeight((short) 350);
		
		CellStyle titleCellStyle = workbook.createCellStyle();
		
		titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
		titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFPalette palette = ((HSSFWorkbook) workbook).getCustomPalette();
		palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 147, (byte) 205,(byte) 221);
		titleCellStyle.setFillForegroundColor(HSSFColor.LIME.index); // 前景色
		titleCellStyle.setFillBackgroundColor(HSSFCellStyle.THICK_FORWARD_DIAG);
		
		Font titleFont = workbook.createFont(); // 字体
		
		titleFont.setFontHeightInPoints((short) 12);
		titleFont.setFontName("宋体");
		
		titleCellStyle.setFont(titleFont);
		
		titleCellStyle.setBorderBottom((short) 1);
		titleCellStyle.setBorderLeft((short) 1);
		titleCellStyle.setBorderRight((short) 1);
		titleCellStyle.setBorderTop((short) 1);
		
		titleCellStyle.setWrapText(true);
		
		HSSFCell cell = null; // 列
		for (int i = 0; i < titles.length; i++) {
			cell = titleRow.createCell(i);
			
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(titles[i]);
			cell.setCellStyle(titleCellStyle);
		}
		
		List<DownloadToExcelByYongHuJson> list = dataService.getYongHuSYQK();
		
		DownloadToExcelByYongHuJson downloadToExcelByYongHuJson = null;
		
		HSSFRow row = null; // 行
		
		CellStyle cellStyle = workbook.createCellStyle();
		
		cellStyle.setBorderBottom((short) 1);
		cellStyle.setBorderLeft((short) 1);
		cellStyle.setBorderRight((short) 1);
		cellStyle.setBorderTop((short) 1);
		
		HSSFCell cellZhucesj = null;
		HSSFCell cellSoukh = null;
		HSSFCell cellShoujih = null;
		HSSFCell cellSsze = null;
		HSSFCell cellCzze = null;
		HSSFCell cellZfze = null;
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		for (int i = 1; i <= list.size(); i++) {
			downloadToExcelByYongHuJson = list.get(i-1);
			
			row = sheet.createRow(i);
			
			cellZhucesj = row.createCell(0);
			cellSoukh = row.createCell(1);
			cellShoujih = row.createCell(2);
			cellSsze = row.createCell(3);
			cellCzze = row.createCell(4);
			cellZfze = row.createCell(5);
			
			cellZhucesj.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellZhucesj.setCellValue(sdf.format(downloadToExcelByYongHuJson.getZhucesj()));
			cellZhucesj.setCellStyle(cellStyle);
			
			cellSoukh.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellSoukh.setCellValue(downloadToExcelByYongHuJson.getSoukh());
			cellSoukh.setCellStyle(cellStyle);
			
			cellShoujih.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellShoujih.setCellValue(downloadToExcelByYongHuJson.getShoujih());
			cellShoujih.setCellStyle(cellStyle);
			
			cellSsze.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellSsze.setCellValue(downloadToExcelByYongHuJson.getSsze());
			cellSsze.setCellStyle(cellStyle);
			
			cellCzze.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellCzze.setCellValue(downloadToExcelByYongHuJson.getCzze());
			cellCzze.setCellStyle(cellStyle);
			
			cellZfze.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellZfze.setCellValue(downloadToExcelByYongHuJson.getZfze());
			cellZfze.setCellStyle(cellStyle);
		}
		
		OutputStream out = response.getOutputStream();
		
		workbook.write(out);
		
		out.flush();
		out.close();
	}
	
}